#!/usr/bin/perl

# Take the LCS1 ASCII catalogue and insert it into the new
# ATCA calibrator database, checking that we don't add any
# sources that are already present, and correctly filling in
# the catalogue fields etc.

use strict;
use DBI;

# connect to the ATCA calibrator database
my $dsn="dbi:Pg:dbname=atca;host=mentok;port=5432";
my $db_user="postgres";
my $db_password="postgres";
my $dbh=DBI->connect($dsn,$db_user,$db_password,
		     {RaiseError => 0, AutoCommit => 1,
		      PrintError => 1});

# read in the LCS1 catalogue
my @lcs1_catalogue;
open(LCS,"lcs1_cat.txt");
while(<LCS>){
    chomp;
    my $line=$_;
    if ($line=~/^\#/){
	next;
    }
    my @els=split(/\s+/,$line);
    my %lcs1_source=( 'name' => $els[0],
		      'catalogue' => 'lcs1',
		      'ra' => $els[2].':'.$els[3].':'.$els[4],
		      'dec' => $els[5].':'.$els[6].':'.$els[7] );
    

    push @lcs1_catalogue,\%lcs1_source;
}
close(LCS);

my $replaced=0;
my $replaced_samename=0;
my $added=0;

for (my $i=0;$i<=$#lcs1_catalogue;$i++){
    my $lc=$lcs1_catalogue[$i];
    print "Source: ".$lc->{'name'}.", catalogue: ".$lc->{'catalogue'}.
	", ra: ".$lc->{'ra'}.", dec: ".$lc->{'dec'}."\n";
    my $closest_query="SELECT name,ra,dec,".
	"angular_separation(ra_to_decimal(ra),dec_to_decimal(dec),".
	"ra_to_decimal('".$lc->{'ra'}."'),dec_to_decimal('".$lc->{'dec'}."'))".
	" AS angsep FROM calibrators ORDER BY angsep LIMIT 1";
    my $closest_handle=$dbh->prepare($closest_query);
    $closest_handle->execute;
    my @closest_response=$closest_handle->fetchrow();
    $closest_handle->finish;
    if ($closest_response[3]<0.01){
	print "  closest: ".$closest_response[0]." (".$closest_response[1].",".
	    $closest_response[2].") @ ".$closest_response[3]." degrees\n";
	# we replace this source's RA and Dec with that from LCS1, and make
	# a note of this
	my $replace_query="UPDATE calibrators SET (ra,dec,info";
	if ($closest_response[0] eq lc($lc->{'name'})){
	    # it has the same name, so we also say this came from the
	    # LCS1 catalogue
	    $replace_query.=",catalogue";
	}
	$replace_query.=")=('".$lc->{'ra'}."','".$lc->{'dec'}."','".
	    $lc->{'catalogue'}."'";
	if ($closest_response[0] eq lc($lc->{'name'})){
	    $replace_query.=",'".$lc->{'catalogue'}."'";
	}
	$replace_query.=") WHERE name='".$closest_response[0]."'";
	print $replace_query."\n";
	my $replace_handle=$dbh->prepare($replace_query);
	$replace_handle->execute;
	$replace_handle->finish;
	$replaced++;
    } else {
	my $samename_query="SELECT name,ra,dec FROM calibrators WHERE ".
	    "name='".lc($lc->{'name'})."'";
	my $samename_handle=$dbh->prepare($samename_query);
	$samename_handle->execute;
	my @samename_response=$samename_handle->fetchrow();
	$samename_handle->finish;
	if (@samename_response){
	    print "  same name found: ".$samename_response[0]." (".
		$samename_response[1].",".$samename_response[2].")\n";
	    # we replace this source's RA and Dec with that from LCS1,
	    # and make a note of it
	    my $replace_query="UPDATE calibrators SET (ra,dec,info,catalogue".
		")=('".$lc->{'ra'}."','".$lc->{'dec'}."','".
		$lc->{'catalogue'}."','".$lc->{'catalogue'}."'".
		") WHERE name='".$closest_response[0]."'";
	    print $replace_query."\n";
	    my $replace_handle=$dbh->prepare($replace_query);
	    $replace_handle->execute;
	    $replace_handle->finish;
	    $replaced_samename++;
	} else {
	    # we have to add this source to the calibrator database
	    my $addsource_query="INSERT INTO calibrators (name,ra,dec,".
		"catalogue,info,type,bandpass_21cm,bandpass_13cm,".
		"bandpass_6cm,bandpass_3cm,bandpass_12mm,bandpass_7mm,".
		"bandpass_3mm) VALUES ('".lc($lc->{'name'})."','".
		$lc->{'ra'}."','".$lc->{'dec'}."','".$lc->{'catalogue'}.
		"','".$lc->{'catalogue'}."','p',FALSE,FALSE,FALSE,".
		"FALSE,FALSE,FALSE,FALSE)";
	    print $addsource_query."\n";
	    my $addsource_handle=$dbh->prepare($addsource_query);
	    $addsource_handle->execute;
	    $addsource_handle->finish;
	    $added++;
	}
    }
}

$dbh->disconnect;
    

print "added ".$added." sources, improved positions for ".$replaced." sources, and changed ".
    "positions for ".$replaced_samename." sources\n";
